ALTER PROCEDURE CPA_GPA(@studentId VARCHAR(5))
AS
DECLARE @totalCredits AS NUMERIC(3, 0);
DECLARE @GPA AS FLOAT;

SELECT year, semester, SUM(credits * mark) / SUM(credits) AS GPA, 
	(SELECT SUM(credits * mark) / SUM(credits)
		FROM course
			JOIN
			(SELECT * FROM takes AS T
			WHERE
				((T.year < T1.year) OR (T.year = T1.year AND T.semester <= T1.semester)) AND
				T.ID=@studentId AND T.year >= ALL(SELECT year FROM takes WHERE ID=@studentId AND course_id = T.course_id) AND
				T.semester >= ALL (SELECT semester FROM takes WHERE ID=@studentId AND course_id = T.course_id)) AS TT
			ON course.course_id = TT.course_id
			JOIN mapping_mark ON TT.grade = mapping_mark.grade
	WHERE ((year < T1.year) OR (year = T1.year AND semester <= semester))) AS CPA
FROM (SELECT * FROM takes
	WHERE ID=@studentId) AS T1 JOIN mapping_mark ON T1.grade = mapping_mark.grade JOIN course ON T1.course_id = course.course_id
GROUP BY year, semester
ORDER BY year, semester;